﻿using MathNet.Numerics.Distributions;
using NPOI.SS.UserModel;
using Sdk.Converts;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sdk.Converts
{
    public class MySQLConvert : ISQLConvert
    {
        DocConverterOptions opt =new DocConverterOptions();

        public string ToSQL(ReadDocResult readDocResult, DocConverterOptions options)
        {
            opt = options;
            
            List<string> sqlParagraphs = new List<string>();

            foreach (var table in readDocResult.Tables)
            {
                List<string> sqlFields = new List<string>();
                List<string> sqlIndexs = new List<string>();
                foreach (var field in table.StructFields)
                {
                    string pk = field.IsPrimaryKey ? "PRIMARY KEY" : "";
                    string ai = field.IsAutoGen ? "AUTO_INCREMENT" : "";
                    string df = field.Default.Trim();
                    string notNull = field.IsNullable ? "" : "NOT NULL";
                    string comment = string.IsNullOrWhiteSpace(field.FieldComment) ? "" : $"COMMENT '{field.FieldComment}'";
                    if (!string.IsNullOrWhiteSpace(df))
                    {
                        df = "default " + df;
                    }
                    //string sqlField = $"`{field.FieldName}` {field.FieldTypeDb} {notNull} {ai} {pk} {df} {comment} {comma} # {field.FieldComment}".Trim();
                    string sqlField = $"`{field.FieldName}` {field.FieldTypeDb} {notNull} {ai} {pk} {df} {comment}".Trim();
                    sqlFields.Add(sqlField);

                    if (field.IsIndex)
                    {
                        string sqlIndex = $"INDEX idx{field.FieldName}({field.FieldName}) ";
                        sqlIndexs.Add(sqlIndex);
                    }
                }

                var sqlParagrap = $"## {table.Comment} \n";

                // Drop语句
                if (opt.IsWithDrop)
                {
                    sqlParagrap += $"DROP TABLE IF EXISTS `{table.TableName}`; \n";
                }

                // 建表语句
                sqlParagrap += $"CREATE TABLE `{table.TableName}` ( \n";
                if (sqlFields.Count > 0)
                {
                    sqlParagrap += string.Join(",\n", sqlFields);
                }
                if (sqlIndexs.Count > 0)
                {
                    sqlParagrap += ",\n";
                    sqlParagrap += string.Join(",\n", sqlIndexs);
                    sqlParagrap += "\n";
                }
                else
                {
                    sqlParagrap += "\n";
                }
                sqlParagrap += $") DEFAULT CHARACTER SET {opt.DbCharSet} \n";
                if (!string.IsNullOrWhiteSpace(table.Comment))
                {
                    sqlParagrap += $"COMMENT = '{table.Comment}' \n";
                }
                sqlParagrap += ";\n";

                sqlParagraphs.Add(sqlParagrap);

                // 初始数据
                if (table.RowDatas.Count > 0)
                {
                    List<string> sqlInsertFields = table.DataFields.Select(x => $"`{x.FieldName}`").ToList();
                    List<string> sqlInsertValues = new List<string>();
                    foreach (var row in table.RowDatas)
                    {
                        List<string> values = new List<string>();
                        foreach (var field in table.DataFields)
                        {
                            var cell = row.CellDatas.FirstOrDefault(x => x.FieldName.Equals(field.FieldName, StringComparison.OrdinalIgnoreCase));
                            if (cell == null) continue;
                            switch (field.FieldType)
                            {
                                case FieldTypeEnum.String:
                                    values.Add($"'{cell.CellText}'");
                                    break;
                                case FieldTypeEnum.DateTime:
                                    {
                                        if (cell.CellText.Contains("now", StringComparison.OrdinalIgnoreCase))
                                        {
                                            values.Add($"{cell.CellText}");
                                        }
                                        else
                                        {
                                            values.Add($"'{cell.CellText}'");
                                        }
                                    }
                                    break;
                                case FieldTypeEnum.Int:
                                case FieldTypeEnum.Decimal:
                                case FieldTypeEnum.Float:
                                case FieldTypeEnum.Double:
                                case FieldTypeEnum.Long:
                                    values.Add($"{cell.CellText}");
                                    break;
                                default:
                                    values.Add($"'{cell.CellText}'");
                                    break;
                            }
                        }
                        sqlInsertValues.Add("(" + string.Join(",", values) + ")");
                    };
                    var sqlInsert = $"INSERT INTO `{table.TableName}`\n";
                    sqlInsert += "(" + string.Join(",", sqlInsertFields) + ")\n";
                    sqlInsert += "VALUES\n";
                    sqlInsert += string.Join(",\n", sqlInsertValues) + "\n";
                    sqlInsert += ";\n";

                    sqlParagraphs.Add(sqlInsert);
                }

                //Table范围内的sql语句
                if (table.ExtCodes.Count > 0)
                {
                    var sqlTableExtCode = string.Join(";\n", table.ExtCodes) + "\n";
                    sqlParagraphs.Add(sqlTableExtCode);
                }
            }

            //其他SQL语句
            var sqlOther = string.Join(";\n", readDocResult.ExtCodes) + "\n";
            sqlParagraphs.Add(sqlOther);

            return string.Join("\n\n", sqlParagraphs); ;
        }
    }
}
